package com.chinasoft.mapper;

import com.chinasoft.pojo.TeacherCount;
import com.chinasoft.pojo.TeacherList;
import com.chinasoft.pojo.Userlist;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface TeacherMapper {
    @Select("select * from teacherlist")
    List<TeacherList> findAll();
    @Select("SELECT * FROM teacherlist WHERE id = #{id}")
    TeacherList getById(Integer id);

    @Select("select * from teacherlist")
    List<TeacherList> findPage(TeacherList teacherList);

    @Select("SELECT * FROM teacherlist WHERE classtype = #{classtype}")
    List<TeacherList> getByClass(String classtype);

    @Select("SELECT * FROM teacherlist WHERE state = #{state}")
    List<TeacherList> getByState(@Param("state")String state);

    @Select("SELECT * FROM teacherlist WHERE teachername = #{teachername}")
    List<TeacherList> getByTeacher(@Param("teachername")String teachername);

    @Select("SELECT COUNT(*) FROM teacherlist WHERE teachername = #{teachername} AND classtype = #{classtype} AND time = #{time}")
    Integer getTime(@Param("teachername")String teachername, @Param("classtype") String classtype, @Param("time") String time);

    @Select("<script> SELECT * from teacherlist" +
            "<where>"+
            "<if test='classname != null'>and classname like concat('%',#{classname},'%') </if> "+
            " <if test='teachername != null'>and teachername like concat('%',#{teachername},'%') </if> "+
            " <if test='classtype != null'>and classtype like concat('%',#{classtype},'%') </if> " +
            " <if test='del != null'>and del != #{del} </if> " +
            " <if test='state != null'>and state = #{state} </if> " +
            "</where>"+
            "</script>")
    List<TeacherList> findBy(TeacherList list);

    @Select("SELECT DISTINCT cd.* " +
            " FROM teacherlist cd " +
            " JOIN userlist ul ON cd.id = ul.classid " +
            " WHERE ul.teachername = #{teachername} AND ul.del = #{del}")
    List<TeacherList> find1(TeacherList list);

    @Update("UPDATE teacherlist SET soild = #{soild} WHERE id = #{id}")
    int updateStatusByOrderCode(@Param("id") Integer id, @Param("soild")String soild);

    @Update("UPDATE teacherlist SET state = #{state} WHERE id = #{id}")
    int updateState(@Param("id") Integer id, @Param("state")String state);

    @Select("SELECT  classtype, COALESCE(SUM(CONVERT(soild, SIGNED)),0) AS total_type " +
                    " FROM teacherlist " +
                    " GROUP BY classtype ")
    List<TeacherCount> findbytype();


    @Insert("insert into teacherlist " +
            " (picture,classname,teachername,classteacher,classtype,number,money,usertools,context,time) " +
            " values (#{picture},#{classname},#{teachername},#{classteacher},#{classtype}, " +
            " #{number},#{money},#{usertools},#{context},#{time}) ")
    int insert(TeacherList teacherlist);

    @Update("UPDATE teacherlist SET del = #{del} WHERE id = #{id}")
    int updateDel(@Param("id") Integer id, @Param("del")String del);

    @Update("UPDATE userlist ul " +
            " JOIN teacherlist tl ON ul.classid = tl.id " +
            " SET ul.del = #{del} " +
            " WHERE tl.id = #{id} ")
    int update1(@Param("id") Integer id, @Param("del")String del);

    @Delete("UPDATE FROM teacherlist SET del = #{del} WHERE teachername = #{teachername}")
    int deleteByName(@Param("teachername") String teachername, @Param("del")String del);

    @Update("UPDATE teacherlist SET classname=#{classname},classtype=#{classtype},number=#{number}, " +
            "money=#{money}, usertools=#{usertools} WHERE id = #{id}")
    int update(TeacherList teacherList);
    //统计每月不同类型课程的销售数量
    @Select("SELECT DATE_FORMAT(STR_TO_DATE(paytime,'%Y/%m/%d %H:%i'), '%Y-%m') AS month, " +
            " classtype, COUNT(*) AS total_type " +
            " FROM userlist " +
            " GROUP BY month,classtype " +
            " ORDER BY month,classtype ")
    List<TeacherCount> find();
}
